let sql = require('../sql/sql');
let data = require('../libs/data');
var db = require('../libs/db');
var utils = require('../libs/utils');
var shortId = require('shortid');
var Ac = db.Ac;
var Dealer = db.Dealer;
var Promsise = require('bluebird');
var jwt = require("jsonwebtoken");
var Activity = db.Activity;
var client = require('redis').createClient();
function randomWord(randomFlag, min, max) {
    var str = "",
        range = min,
        arr = ['0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z'];

    // 随机产生
    if (randomFlag) {
        range = Math.round(Math.random() * (max - min)) + min;
    }
    for (var i = 0; i < range; i++) {
        pos = Math.round(Math.random() * (arr.length - 1));
        str += arr[pos];
    }
    return str;
}
//验证短信码
function verifysms(code) {
    return true;
}

var getIsActivity = function(codeid) {
    console.log('codeid-----',codeid);
    return new Promise(function (resolve, reject) {
        client.get('activityid_start_time_' + codeid, function (err, object) {
            if (!err) {
                console.log('rows-------111--',object);
                if(object != null){
                    resolve('2');
                }else{
                    resolve('1');
                }
            }
        });
    });
}

Array.prototype.contains = function(val)
{
    for (var i = 0; i < this.length; i++)
    {
        if (this[i] == val)
        {
            return true;
        }
    }
    return false;
};

module.exports = {
    add (req, res) {
        var name = req.body.name;
        var address = req.body.address;
        var number = req.body.number;
        var lunci = req.body.lunci;
        var token = utils.getToken(req.headers);
        var userData = jwt.verify(token, 'secret');
        console.log('userData',userData);
        var acid = shortId.generate();

        Dealer.findOne({
            where: {
                userid: userData.userid,
            },
        }).then(function (result) {
            var acData = {
                acid: acid,
                userid: userData.userid,
                name: name,
                address: address,
                tag:req.body.tag,
                number:number,
                lunci:lunci,
                company: req.body.company,
                office: req.body.office,
                city: req.body.city,
                host: req.body.host,
                store: req.body.store,
                hotel: req.body.hotel,
                jbtime: req.body.jbtime,
                hdtime: req.body.hdtime,
                qunti: req.body.qunti,
                ds: req.body.ds
            };
            console.log('添加数据：',acData);
            return Ac.create(acData).then(function (result) {
                data.code = '200';
                data.msg = '添加数据成功';
                data.data = '';
                res.status(200).send(JSON.stringify(data));
            }).catch(function (err) {
                data.code = '101';
                data.msg = '添加数据失败';
                data.data = '';
                res.status(200).send(JSON.stringify(data));
            });
        }).catch(function (err) {
            data.code = '101';
            data.msg = '添加数据失败';
            data.data = '';
            res.status(200).send(JSON.stringify(data));
        });
    },
    edit (req, res) {
        var name = req.body.name;
        var address = req.body.address;
        var number = req.body.number;
        var lunci = req.body.lunci;
        var token = utils.getToken(req.headers);
        var userData = jwt.verify(token, 'secret');
        console.log('userData',userData);
        var acid = req.body.acid;

        Dealer.findOne({
            where: {
                userid: userData.userid,
            },
        }).then(function (result) {
            var acData = {
                name: name,
                address: address,
                tag:req.body.tag,
                number:number,
                lunci:lunci,
                company: req.body.company,
                office: req.body.office,
                city: req.body.city,
                host: req.body.host,
                store: req.body.store,
                hotel: req.body.hotel,
                jbtime: req.body.jbtime,
                hdtime: req.body.hdtime,
                qunti: req.body.qunti,
                ds: req.body.ds
            };
            console.log('修改数据：',acData);
            return Ac.update(acData,{
                where: {
                    acid: acid
                }
            }).then(function (result) {
                data.code = '200';
                data.msg = '修改数据成功';
                data.data = '';
                res.status(200).send(JSON.stringify(data));
            }).catch(function (err) {
                data.code = '101';
                data.msg = '修改数据失败';
                data.data = '';
                res.status(200).send(JSON.stringify(data));
            });
        }).catch(function (err) {
            console.log(err);
            data.code = '101';
            data.msg = '修改数据失败';
            data.data = '';
            res.status(200).send(JSON.stringify(data));
        });
    },
    findList(req, res){
        var token = utils.getToken(req.headers);
        var userData = jwt.verify(token, 'secret');
        var tag = '1';
        console.log('userData: ',userData);
        var sql = 'select a.id,a.acid,a.name,a.address,a.number,a.lunci,a.activityArea,DATE_FORMAT(a.jbtime,"%Y-%m-%d") times,store,hotel,jbtime,hdtime,b.rate from jyj_ac a  left join (select * from jyj_topicdes b GROUP BY b.acid) b on a.acid = b.acid where a.userid = :userid and DATE_FORMAT(a.jbtime, "%Y-%m-%d") >= DATE_FORMAT(NOW(),"%Y-%m-%d") and a.tag=:tag order by id';
        db.sequelize.query(sql, {
            replacements: {userid: userData.userid,tag:tag},
            type: db.sequelize.QueryTypes.SELECT
        }).then(function (result) {
            console.log(result.length);
            data.code = '200';
            data.msg = '查询数据成功';
            data.data = result;
            res.status(200).send(JSON.stringify(data));
        }).catch(function (err) {
            data.code = '101';
            data.msg = '查询数据失败';
            res.status(200).send(JSON.stringify(data));
        });

    },
    findActivityList(req, res){
        var token = utils.getToken(req.headers);
        var userData = jwt.verify(token, 'secret');
        var acid = req.body.acid;
        var userid = userData.userid;
        var mainData = {
            ac: [],
            activityList: []
        }
        var sql = 'SELECT DATE_FORMAT(a.ctime,"%Y-%m-%d") ctime,a.name,a.lunci,a.address,a.acid,a.activityArea,a.jbtime,a.store,a.hotel from jyj_ac a where a.userid=:userid and a.acid=:acid ';


        return db.sequelize.query(sql, {
            replacements: {userid: userid, acid: acid},
            type: db.sequelize.QueryTypes.SELECT
        }).then(function (result) {
            console.log(result);
            mainData.ac = result[0];
            var sq11 = 'select date_format(a.createtime, "%H:%i") time, a.code activitycode, a.codeid, a.active, group_concat(a.answer ORDER BY a.id) answers, GROUP_CONCAT(a.okanswer ORDER BY a.id) okanswer FROM ( SELECT a.createtime, a.`code`, a.codeid, a.active, CASE b.okanswer WHEN "A" THEN b.answer1 WHEN "B" THEN b.answer2 WHEN "C" THEN b.answer3 WHEN "D" THEN b.answer4 END answer, b.okanswer, b.id FROM ( SELECT b.createtime, b.`code`, b.codeid, b.id, b.active FROM jyj_ac a, jyj_activity b WHERE a.acid = b.acid AND a.userid = :userid AND a.acid = :acid ) a, jyj_topic b WHERE a.codeid = b.codeid ) a GROUP BY a.createtime, a. CODE, a.codeid ORDER BY a.id';
            db.sequelize.query(sq11, {
                replacements: {userid: userid, acid: acid},
                type: db.sequelize.QueryTypes.SELECT
            }).then(function (result) {
                console.log('结果：', result);
                mainData.activityList = result;
                (async () => {
                    for (i = 0; i < mainData.activityList.length; i++) {
                        var item = mainData.activityList[i];
                        item['answer'] = [];
                        console.log(item);
                        for (j = 0; j < item.answers.split(',').length; j++) {
                            var one = {
                                title: item.answers.split(',')[j],
                                tag: item.okanswer.split(',')[j]
                            }
                            item['answer'].push(one);
                        }
                        if(item.active=='1'){
                            item['status'] = await getIsActivity(item.codeid);
                        }else if(item.active=='2'){
                            item['status'] = '4'
                        }
                    }
                    data.code = '200';
                    data.msg = '查询数据成功';
                    data.data = mainData;
                    console.log('mainData---',mainData);
                    res.status(200).send(JSON.stringify(data));
                })();
            })
        }).catch(function (err) {
            data.code = '101';
            data.msg = '查询数据失败';
            res.status(200).send(JSON.stringify(data));
        });

    },
    findResultList(req, res){
        var token = utils.getToken(req.headers);
        var userData = jwt.verify(token, 'secret');
        var tag = '2';
        var sql = 'select a.acid,a.name,a.address,DATE_FORMAT(a.ctime,"%Y-%m-%d") times,DATE_FORMAT(a.ctime,"%m-%d") ctime,CONCAT(a.activityArea,"分站赛") ctitle from jyj_ac a where a.userid = :userid and a.tag=:tag';
        db.sequelize.query(sql, {
            replacements: {userid: userData.userid,tag:tag},
            type: db.sequelize.QueryTypes.SELECT
        }).then(function (result) {
            console.log(result.length);
            data.code = '200';
            data.msg = '查询数据成功';
            data.data = result;
            res.status(200).send(JSON.stringify(data));
        }).catch(function (err) {
            data.code = '101';
            data.msg = '查询数据失败';
            res.status(200).send(JSON.stringify(data));
        });

    },
    findUserTopicList(req, res){
        var acid = req.body.acid;
        var sql = 'SELECT a.acid, a.codeid, a.id, GROUP_CONCAT(a.username) username, GROUP_CONCAT(a.isok) isok FROM ( SELECT a.acid, a.codeid, a.id, a.userid, b.username, CASE WHEN CONCAT( a.topic1, ",", a.topic2, ",", a.topic3 ) = ( SELECT GROUP_CONCAT(c.okanswer) okanswer FROM jyj_topic c WHERE c.codeid = a.codeid GROUP BY c.codeid ORDER BY c.id ) THEN "1" ELSE "2" END isok FROM ( SELECT a.acid, a.codeid, a.id, a.createtime, b.userid, b.topic1, b.topic2, b.topic3 FROM jyj_activity a LEFT JOIN jyj_usertopic b ON a.codeid = b.codeid WHERE a.acid = :acid ORDER BY a.id ) a LEFT JOIN jyj_user b ON a.userid = b.userid ORDER BY a.id ) a GROUP BY a.codeid';
        var mm = {
            title:'',
            data:[]
        };
        Ac.findOne({
            where: {
                acid: acid
            }
        }).then(function (result) {
            mm.title = result.name
            db.sequelize.query(sql, {
                replacements: {acid: acid},
                type: db.sequelize.QueryTypes.SELECT
            }).then(function (result) {
                for(i=0;i<result.length;i++){
                    var item = result[i];
                    item['okuserList'] = '';
                    item['nouserList'] = '';

                    if(item.username!=null){
                        var users = item.username.split(',');
                        for(j=0;j<users.length;j++){
                            if(item.isok.split(',')[j]=='1'){
                                result[i]['okuserList']+=users[j]+'、';
                            }else{
                                result[i]['nouserList']+=users[j]+'、';
                            }
                        }
                        result[i]['okuserList'] = result[i]['okuserList'].substr(0,result[i]['okuserList'].length-1);
                        result[i]['nouserList'] = result[i]['nouserList'].substr(0,result[i]['nouserList'].length-1);
                    }
                }
                mm.data = result;
                data.code = '200';
                data.msg = '查询数据成功';
                data.data = mm;
                res.status(200).send(JSON.stringify(data));
            }).catch(function (err) {
                data.code = '101';
                data.msg = '查询数据失败';
                res.status(200).send(JSON.stringify(data));
            });
        });

    },
    findUserResultType(req, res){
        var acid = req.body.acid;
        var step = req.body.step;
        var sql = 'SELECT a.acid, a.codeid, a.id, a.userid, b.username, a.totaltime, b.phone, a.step, CASE WHEN CONCAT( a.topic1, ",", a.topic2, ",", a.topic3 ) = ( SELECT GROUP_CONCAT(c.okanswer) okanswer FROM jyj_topic c WHERE c.codeid = a.codeid GROUP BY c.codeid ORDER BY c.id ) THEN "1" ELSE "2" END isok FROM ( SELECT a.acid, a.codeid, a.id, a.createtime, b.userid, b.topic1, b.topic2, b.topic3, b.totaltime, a.step FROM jyj_activity a RIGHT JOIN jyj_usertopic b ON a.codeid = b.codeid WHERE a.acid = :acid ORDER BY a.id ) a left join jyj_user b ON a.userid = b.userid ORDER BY a.totaltime';
        var mm = {
            title:'',
            all:[],
            list:[],
        };
        Ac.findOne({
            where: {
                acid: acid
            }
        }).then(function (result) {
            mm.title = result.name;
            mm.number = result.number;
            Activity.findAll({
                where: {
                    acid: acid
                }
            }).then(function (results) {
                var data1Sql = 'SELECT a.*, b.mgroup FROM ( SELECT a.acid, a.codeid, a.id, a.createtime, a.userid, a.topic1, a.topic2, a.topic3, a.totaltime, b.username, b.phone FROM jyj_usertopic a, jyj_user b WHERE a.userid = b.userid AND a.acid = "'+acid+'" AND b.tag = "2" ORDER BY a.id ) a, ( SELECT @mgroup := @mgroup + 1 AS mgroup, p.* FROM ( SELECT b.codeid FROM jyj_ac a, jyj_activity b WHERE a.acid = b.acid AND a.acid = "'+acid+'" ) p, (SELECT @mgroup := 0) r ) b WHERE a.codeid = b.codeid ORDER BY a.totaltime';
                db.sequelize.query(data1Sql, {
                    replacements: {acid: acid},
                    type: db.sequelize.QueryTypes.SELECT
                }).then(function (dataResult) {
                    mm.all = dataResult;

                    var data1Sql1 = 'SELECT @mgroup := @mgroup + 1 AS mgroup, p.* FROM ( SELECT b.codeid,b.id FROM jyj_ac a, jyj_activity b WHERE a.acid = b.acid AND a.acid = "'+acid+'" ) p, (SELECT @mgroup := 0) r order by p.id';
                    db.sequelize.query(data1Sql1, {
                        replacements: {acid: acid},
                        type: db.sequelize.QueryTypes.SELECT
                    }).then(function (dataResult1) {
                        if(dataResult1!=null){
                            var data1Sql2 = 'SELECT a.*, b.mgroup FROM ( SELECT a.acid, a.codeid, a.id, a.createtime, a.userid, a.topic1, a.topic2, a.topic3, a.totaltime, b.username, b.phone, CASE WHEN b.tag = "2" THEN "1" ELSE "2" END isok FROM jyj_usertopic a, jyj_user b WHERE a.userid = b.userid AND a.acid = "'+acid+'" ORDER BY a.id ) a, ( SELECT @mgroup := @mgroup + 1 AS mgroup, p.* FROM ( SELECT b.codeid FROM jyj_ac a, jyj_activity b WHERE a.acid = b.acid AND a.acid = "'+acid+'" ) p, (SELECT @mgroup := 0) r ) b WHERE a.codeid = b.codeid ORDER BY a.totaltime';
                            db.sequelize.query(data1Sql2, {
                                replacements: {acid: acid},
                                type: db.sequelize.QueryTypes.SELECT
                            }).then(function (dataResult2) {
                                dataResult1.forEach(function (item1,i) {
                                    var items = {
                                        yes:[],
                                        no:[]
                                    };
                                    dataResult2.forEach(function (item,i) {
                                        if(item1.codeid==item.codeid){
                                            if(item.isok=='1'){
                                                items.yes.push(item);
                                            }else if(item.isok=='2'){
                                                items.no.push(item);
                                            }
                                        }
                                    })
                                    mm.list.push(items);
                                })
                                data.code = '200';
                                data.msg = '查询数据成功';
                                data.data = mm;
                                console.log(mm);
                                res.status(200).send(JSON.stringify(data));
                            })
                        }
                    })
                }).catch(function (err) {
                    data.code = '101';
                    data.msg = '查询数据失败';
                    res.status(200).send(JSON.stringify(data));
                });
            });
        });

    },
    /**
     * 显示大屏数据
     * @param req
     * @param res
     */
    findDPResultList(req, res){
        var acid = req.body.acid;
        var page = req.body.page;
        var pagecount = req.body.pagecount;
        var mgroup = req.body.mgroup;
        if(pagecount==undefined || pagecount==null || pagecount==''){
            pagecount = 25;
        }
        var mm = {
            title:'',
            group:[],
            all:[],
            total:0,
            oktotal:0
        };

        var titleSql = 'SELECT a.name,a.lunci FROM jyj_ac a where a.acid=:acid';
        db.sequelize.query(titleSql, {
            replacements: {acid: acid},
            type: db.sequelize.QueryTypes.SELECT
        }).then(function (titleResult) {
            if(titleResult!=null){
                mm.title = titleResult[0].name+''+(titleResult[0].lunci=='1'?'第一轮':'第二轮');
                mm.
                console.log(mm.title);
                var data1Sql = 'SELECT @rownum :=@rownum + 1 AS rownum, p.* FROM ( SELECT a.userid, a.username, a.phone, b.totaltime, b.rate FROM jyj_user a, jyj_topicdes b WHERE a.userid = b.userid AND b.acid = :acid ) p, (SELECT @rownum := 0) r ORDER BY rate DESC, totaltime limit '+(page-1)*pagecount+','+pagecount+'';
                db.sequelize.query(data1Sql, {
                    replacements: {acid: acid},
                    type: db.sequelize.QueryTypes.SELECT
                }).then(function (dataResult) {
                    mm.all = dataResult;
                    var data1Sql4 = 'select count(1) total from (SELECT @rownum :=@rownum + 1 AS rownum, p.* FROM ( SELECT a.userid, a.username, a.phone, b.totaltime, b.rate FROM jyj_user a, jyj_topicdes b WHERE a.userid = b.userid AND b.acid = :acid ) p, (SELECT @rownum := 0) r) a';
                    db.sequelize.query(data1Sql4, {
                        replacements: {acid: acid},
                        type: db.sequelize.QueryTypes.SELECT
                    }).then(function (dataResult4) {
                        mm.total = dataResult4[0].total;
                        data.code = '200';
                        data.msg = '查询数据成功';
                        data.data = mm;
                        res.status(200).send(JSON.stringify(data));
                    })
                }).catch(function (err) {
                    data.code = '101';
                    data.msg = '查询数据失败';
                    res.status(200).send(JSON.stringify(data));
                });
            }
        });
    },
    //获取活动举行时间
    getActivityTime(req,res){
        client.get('activityid_start_time_' + req.body.codeid, function (err, object) {
            if (!err && object != null) {
                data.code = '200';
                data.msg = '查询数据成功';
                data.data = {
                    time: object,
                    nowtime: Date.parse(new Date())
                }
                console.log(JSON.stringify(data));
                res.status(200).send(JSON.stringify(data));
            }else{
                data.code = '101';
                data.msg = '查询数据失败';
                res.status(200).send(JSON.stringify(data));
            }
        });
    },
    findDealer(req, res){
        var acid = req.body.acid;
        var step = req.body.step;
        Ac.findOne({
            where: {
                acid: acid
            }
        }).then(function (result) {
            data.code = '200';
            data.msg = '查询数据成功';
            data.data = result;
            res.status(200).send(JSON.stringify(data));
        }).catch(function (err) {
            data.code = '101';
            data.msg = '查询数据失败';
            res.status(200).send(JSON.stringify(data));
        });

    },
    findUserResults(req, res){
        // var acid = 'rJaEuT6fz';
        var acid = req.body.acid;
        var sql = 'select * from ( select a.*, c.jbtime, c.store, case when a.rate = 3 then "2" else "1" end res, b.username from jyj_topicdes a left join jyj_user b on a.userid = b.userid left join jyj_ac c on a.acid = c.acid where a.acid = :acid ) a group by a.res, a.activityid order by a.activityid, a.jbtime';
        db.sequelize.query(sql, {
            replacements: {acid: acid},
            type: db.sequelize.QueryTypes.SELECT
        }).then(function (result) {
            console.log(result.length);

            db.sequelize.query('select *,DATE_FORMAT(a.createtime,"%H:%i") ctimes,"" as success,"" as error  from jyj_activity a left join jyj_ac b on a.acid = b.acid where a.acid = :acid order by a.id', {
                replacements: {acid: acid},
                type: db.sequelize.QueryTypes.SELECT
            }).then(function (activityResult) {


                activityResult.forEach(function (item,i) {
                    result.forEach(function (items,i) {
                        console.log(item);
                        if(item.codeid==items.activityid){
                            if(items.res=='2'){
                                item.success +=  items.username+',';
                            }else{
                                item.error +=  items.username+',';
                            }
                        }
                    })
                })

                console.log(activityResult);

                data.code = '200';
                data.msg = '查询数据成功';
                data.data = activityResult;
                res.status(200).send(JSON.stringify(data));
            })
        }).catch(function (err) {
            data.code = '101';
            data.msg = '查询数据失败';
            res.status(200).send(JSON.stringify(data));
        });
    },
    findLSResults(req, res){
        var token = utils.getToken(req.headers);
        var userData = jwt.verify(token, 'secret');
        var userid = userData.userid;
        var sql = 'SELECT a.store,a.acid,a.jbtime,a.hdtime,IFNULL(b.err,0) err,IFNULL(b.suc,0) suc from jyj_ac a LEFT JOIN (SELECT a.acid, sum( CASE WHEN a.res = 1 THEN 1 ELSE 0 END ) err, sum( CASE WHEN a.res = 2 THEN 1 ELSE 0 END ) suc FROM ( SELECT a.*, CASE WHEN a.rate = 3 THEN "2" ELSE "1" END res FROM jyj_topicdes a ) a GROUP BY a.acid) b on a.acid = b.acid where a.userid = :userid order by a.id desc;';
        db.sequelize.query(sql, {
            replacements: {userid: userid},
            type: db.sequelize.QueryTypes.SELECT
        }).then(function (result) {
            console.log(result.length);
            data.code = '200';
            data.msg = '查询数据成功';
            data.data = result;
            res.status(200).send(JSON.stringify(data));
        }).catch(function (err) {
            data.code = '101';
            data.msg = '查询数据失败';
            res.status(200).send(JSON.stringify(data));
        });
    }
};